﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace JobLogDAL
{
    public class SQLHELP
    {
        static string connStr = ConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="strCommand">SQL或者存储过程的名称</param>
        /// <param name="comType">命令类型（SQL/存储过程）</param>
        /// <param name="para">参数集合:3个参数</param>
        /// <returns></returns>
        public static DataSet ExecuteQuery(string strSql, CommandType comType, SqlParameter[] param)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connStr))
            {
                try
                {
                    SqlCommand com = new SqlCommand(strSql, con);
                    com.CommandType = comType;                           //命令的类型（SQL/Procedure）

                    //参数分析：
                    if (param != null)
                    {
                        foreach (SqlParameter parameter in param)
                        {
                            //将参数加入到Command对象
                            com.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
                        }
                    }
                    SqlDataAdapter da = new SqlDataAdapter(com);
                    da.Fill(ds);
                }
                catch (SqlException se)
                {
                    con.Close();

                }
            }

            return ds;
        }
        //ExecuteQuery  2个参数(公共服务层,)
        public static DataSet ExecuteQuery(string strSql, CommandType comType)
        {
            return ExecuteQuery(strSql, comType, null);
        }
        // //ExecuteQuery  2个参数(公共服务层,)
        public static DataSet ExecuteQuery(string strSql, params SqlParameter[] para)
        {
            return ExecuteQuery(strSql, CommandType.Text, para);
        }

        //ExecuteQuery  1个参数(公共服务层,)
        public static DataSet ExecuteQuery(string strSql)
        {
            return ExecuteQuery(strSql, CommandType.Text, null);
        }


        /// <summary>
        /// 执行单行单列查询
        /// </summary>
        /// <param name="strCommand">SQL或者存储过程的名称</param>
        /// <param name="comType">命令类型（SQL/存储过程）</param>
        /// <param name="para">参数集合</param>
        /// <returns></returns>
        public static Object ExecuteScalar(string strSql, CommandType comType, params SqlParameter[] para)
        {
            Object objResult = null;                                     //返回数值
            using (SqlConnection con = new SqlConnection(connStr))
            {
                try
                {
                    SqlCommand com = new SqlCommand(strSql, con);
                    com.CommandType = comType;                           //命令的类型（SQL/Procedure）

                    //参数分析
                    if (para != null)
                    {
                        foreach (SqlParameter sp in para)
                        {

                            com.Parameters.AddWithValue(sp.ParameterName, sp.Value);
                        }
                    }
                    con.Open();
                    objResult = com.ExecuteScalar();
                }
                catch (SqlException se)
                {
                    con.Close();
                }
            }//using_end

            return objResult;
        }
        public static Object ExecuteScalar(string strSql, CommandType comType)
        {
            return ExecuteScalar(strSql, comType, null);
        }
        public static Object ExecuteScalar(string strSql)
        {
            return ExecuteScalar(strSql, CommandType.Text, null);
        }
        public static Object ExecuteScalar(string strSql, params SqlParameter[] para)
        {
            return ExecuteScalar(strSql, CommandType.Text, para);
        }



        /// <summary>
        /// 执行非查询
        /// </summary>
        /// <param name="strCommand">SQL或者存储过程的名称</param>
        /// <param name="comType">命令类型（SQL/存储过程）</param>
        /// <param name="para">参数集合</param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(string strSql, CommandType comType, params SqlParameter[] para)
        {
            bool boolResult = false;                                     //返回数值

            using (SqlConnection con = new SqlConnection(connStr))
            {
                con.Open();
                try
                {
                    SqlCommand com = new SqlCommand(strSql, con);
                    com.CommandType = comType;                           //命令的类型（SQL/Procedure）

                    //参数分析
                    if (para != null)
                    {
                        foreach (SqlParameter sp in para)
                        {
                            com.Parameters.AddWithValue(sp.ParameterName, sp.Value);
                        }
                    }
                    int intResult = com.ExecuteNonQuery();
                    if (intResult >= 1)
                    {
                        boolResult = true;
                    }
                }
                catch (SqlException se)
                {
                    con.Close();
                }
            }

            return boolResult;
        }

        public static bool ExecuteNonQuery(string strSql, CommandType comType)
        {
            return ExecuteNonQuery(strSql, comType, null);
        }
        public static bool ExecuteNonQuery(string strSql)
        {
            return ExecuteNonQuery(strSql, CommandType.Text, null);
        }
        public static bool ExecuteNonQuery(string strSql, params SqlParameter[] para)
        {
            return ExecuteNonQuery(strSql, CommandType.Text, para);
        }


        //附加:DataGridView编辑信息
        public static bool EditDataGridView(DataTable dt, string sqlstr)
        {
            bool flag = false;   //操作结果
            int intResult = 0;
            using (SqlConnection sqlCon = new SqlConnection(connStr))
            {

                SqlDataAdapter da = new SqlDataAdapter(sqlstr, sqlCon);
                SqlCommandBuilder scb = new SqlCommandBuilder(da);   //自动产生对应SelectCommand,InsertCommand,UpdateCommand,DeleteCommand
                intResult = da.Update(dt);//将dt中的数据保存到数据库
            }//using__end
            if (intResult >= 1)
            {
                flag = true;
            }
            return flag;
        }
    }
}
